﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.IO.MemoryMappedFiles;
using System.Net;
using System.Runtime.InteropServices;
using System.Text;

public class Aquire
{
    #region Common
    private class SqlContextConnection : IDisposable
    {
        System.Data.SqlClient.SqlConnection connection;
        public SqlContextConnection() :this(true) { }

        public SqlContextConnection(bool bOpen)
        {
            connection = new System.Data.SqlClient.SqlConnection("context connection=true");
            if (bOpen)
                connection.Open();
        }

        void IDisposable.Dispose()
        {
            connection.Dispose();
        }
    }
    #endregion Common
    #region Simple TVF
    #region ZeroToNine
    // CREATE FUNCTION Gorm.ZeroToNine() RETURNS TABLE (PK BIGINT, Name NVARCHAR(4000), Description NVARCHAR(MAX)) EXTERNAL NAME Gorm.Aquire.ZeroToNine
    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "ZeroToNine_Fill")]
    public static IEnumerator ZeroToNine()
    {
        yield return new ZeroToNine_Row(0, "Zero", "We often start counting at zero on computers");
        yield return new ZeroToNine_Row(1, "One", "2nd number");
        yield return new ZeroToNine_Row(2, "Two", "3rd");
        yield return new ZeroToNine_Row(3, "Three", "4th");
        yield return new ZeroToNine_Row(4, "Four", "Fifth number, half way there");
        yield return new ZeroToNine_Row(5, "Five", "Sixth");
        yield return new ZeroToNine_Row(6, "Six", "Seventh");
        yield return new ZeroToNine_Row(7, "Seven", "Eight");
        yield return new ZeroToNine_Row(8, "Eight", "Nineth");
        yield return new ZeroToNine_Row(9, "Nine", "The tenth number");
    }
    public struct ZeroToNine_Row
    {
        public Int64 pk;
        public StringBuilder name;
        public StringBuilder comment;
        public ZeroToNine_Row(Int64 _pk, string _name, string _comment)
            :this()
        {
            pk = _pk;
            name = new StringBuilder(_name);
            comment = new StringBuilder(_comment);
        }
    }
    public static void ZeroToNine_Fill(object row, out SqlInt64 pk, out SqlString name, out SqlString comment)
    {
        ZeroToNine_Row r = (ZeroToNine_Row)row;
        pk = r.pk;
        name = r.name.ToString();
        comment = r.comment.ToString();
    }
    #endregion ZeroToNine
    #endregion Simple TVF
    #region File read
    #region private internal file utilities
    private static IEnumerable<string> File_FileOrFiles(string fileOrFiles)
    {
        if (fileOrFiles.IndexOfAny("/?*".ToCharArray()) > 0)
        {
            bool bRecursive = false;
            string sPath, sSearchPattern;
            bRecursive = false;
            for (int iOption = fileOrFiles.IndexOf('/'); iOption != -1; iOption = fileOrFiles.IndexOf('/'))
            {
                if (fileOrFiles.Length > iOption + 1)
                {
                    if (char.ToLower(fileOrFiles[iOption + 1]) == 's')
                        bRecursive = true;
                    fileOrFiles = fileOrFiles.Remove(iOption, 2);
                }
            }
            fileOrFiles = fileOrFiles.Trim();
            sPath = Path.GetDirectoryName(fileOrFiles);
            sSearchPattern = Path.GetFileName(fileOrFiles);
            foreach (string s in Directory.EnumerateFiles(sPath, sSearchPattern, bRecursive ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly))
                yield return s;
        }
        else
            yield return fileOrFiles;
    }
    #endregion private internal file utilities
    #region RawFileS
    public struct RawFileS_Row
    {
        public Int64 pk;
        public Int64 length;
        public string fn;
        public StringBuilder str;
    }
    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "RawFileS_Fill")]
    public static IEnumerator RawFileS(string fileNames, long offset, long length, long bufferLength)
    {
        RawFileS_Row row;
        row.pk = 1;
        foreach (string fn in File_FileOrFiles(fileNames))
        {
            row.str = new StringBuilder();
            row.fn = fn;
            long lEnd = new FileInfo(fn).Length;
            row.length = length;
            if (row.length == -1)
                row.length = long.MaxValue / 2;
            if (lEnd > offset + row.length)
                lEnd = offset + row.length;
            MemoryMappedFile mmf = null;
            try
            {
                mmf = MemoryMappedFile.CreateFromFile(fn, FileMode.Open);
            }
            catch (Exception) { }
            if (mmf == null)
            { // Error reading file, return empty content
                row.pk = -row.pk;
                yield return row;
                row.pk = -row.pk;
            }
            else
            {
                for (long lLocalStart = offset; lLocalStart < lEnd; lLocalStart += bufferLength)
                {
                    if (lLocalStart + bufferLength >= lEnd)
                        bufferLength = lEnd - lLocalStart;
                    if (bufferLength > 0)
                        using (var accessor = mmf.CreateViewAccessor(lLocalStart, bufferLength))
                        {
                            byte b;
                            for (long i = 0; i < bufferLength; i++)
                            {
                                accessor.Read(i, out b);
                                if (b >= 32)
                                    row.str.Append((char)b);
                                if (b == 13 || row.str.Length == 4000)
                                {
                                    yield return row;
                                    row.str.Length = 0;
                                    row.pk++;
                                }
                            }
                        }
                }
                mmf.Dispose();
            }
            if (row.str.Length > 0)
                yield return row;
        }
    }

    public static void RawFileS_Fill(object row, out Int64 pk, out string fn, out string str)
    {
        RawFileS_Row r = (RawFileS_Row)row;
        pk = r.pk;
        fn = r.fn;
        str = r.str.ToString();
    }

    #endregion RawFileS
    #region RawFile
    public struct RawFile_Row
    {
        public Int64 pk;
        public string fileName;
        public Byte[] b;
        public int maxLength;
    }
    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "RawFile_Fill")]
    public static IEnumerator RawFile(string fn, long offset, int bufferLength, long bufferCount)
    {
        RawFile_Row row;
        row.pk = 1;
        foreach (string sFileName in File_FileOrFiles(fn))
        { // single file
            row.fileName = sFileName;
            long lEnd = new FileInfo(sFileName).Length;
            row.maxLength = bufferLength;
            if (row.maxLength == 0)
                row.maxLength = (int)(lEnd - offset);
            if (lEnd > offset)
            {
                MemoryMappedFile mmf = null;
                try
                {
                    mmf = MemoryMappedFile.CreateFromFile(sFileName, FileMode.Open);
                }
                catch (Exception) { }
                row.b = new Byte[0];
                if (mmf == null)
                { // Error reading file, return empty content
                    row.pk = -row.pk;
                    yield return row;
                    row.pk = -row.pk;
                }
                else
                {
                    for (long lLocalStart = offset, bufferIndex = 0; lLocalStart < lEnd && bufferIndex < bufferCount; lLocalStart += row.maxLength, bufferIndex++)
                    {
                        if (lLocalStart + row.maxLength >= lEnd)
                            row.maxLength = (int)(lEnd - lLocalStart);
                        using (var accessor = mmf.CreateViewAccessor(lLocalStart, row.maxLength))
                        {
                            if (row.b == null || row.b.Length != row.maxLength)
                                row.b = new Byte[row.maxLength];
                            RawFile_CopyData(ref row, accessor, row.maxLength);
                            yield return row;
                            row.pk++;
                        }
                    }
                    mmf.Dispose();
                }
            }
        }
    }

    private unsafe static void RawFile_CopyData(ref RawFile_Row row, MemoryMappedViewAccessor accessor, int bufferLength)
    {
        byte* ptr = (byte*)0;
        accessor.SafeMemoryMappedViewHandle.AcquirePointer(ref ptr);
        Marshal.Copy(IntPtr.Add(new IntPtr(ptr), 0), row.b, 0, (int)bufferLength);
        accessor.SafeMemoryMappedViewHandle.ReleasePointer();
    }

    public static void RawFile_Fill(object row, out Int64 pk, out string fileName, out SqlBinary b)
    {
        RawFile_Row r = (RawFile_Row)row;
        pk = r.pk;
        b = new SqlBinary(r.b);
        fileName = r.fileName;
    }
    #endregion RawFile
    #endregion File read
    #region HTTP read
    #region private internal http utilities
    private static IEnumerable<string> Http_UriOrUris(string uriOrUris)
    {
        foreach (string sUri in uriOrUris.Split("\xa\xd\x9".ToCharArray()))
            if (sUri.Trim().Length > 0)
                yield return sUri.Trim();
    }
    #endregion private internal http utilities
    #region HttpGet
    public struct HttpGet_Row
    {
        public Int64 pk;
        public string uri;
        public string contentType;
        public int contentLength;
        public Byte[] b; // ArrayList?
    }
    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "HttpGet_Fill")]
    public static IEnumerator HttpGet(string uriOrUris, int contentMaxLength)
    {
        HttpGet_Row row;
        row.pk = 1;
        foreach (string sUri in Http_UriOrUris(uriOrUris))
        {
            row.uri = sUri;
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(row.uri);
            request.MaximumAutomaticRedirections = 100;
            request.MaximumResponseHeadersLength = 100;
            request.Credentials = CredentialCache.DefaultCredentials;
            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            row.contentLength = (int)response.ContentLength;
            row.contentType = response.ContentType;

            byte[]b = new byte[contentMaxLength];
            int iTotEnd = response.GetResponseStream().Read(b, 0, contentMaxLength);
            int iNewLen = iTotEnd;
            while (iNewLen > 0 && iTotEnd < contentMaxLength)
            {
                iNewLen = response.GetResponseStream().Read(b, iTotEnd, contentMaxLength - iTotEnd);
                iTotEnd += iNewLen;
            }
            row.b = new byte[iTotEnd];
            for (int i = 0; i < iTotEnd; i++)
                row.b[i] = b[i];
            yield return row;
            response.Close();
            row.pk++;
        }
    }

    public static void HttpGet_Fill(object row, out Int64 pk, out String uri, out String contentType, out Int32 contentLength, out SqlBinary b)
    {
        HttpGet_Row r = (HttpGet_Row)row;
        pk = r.pk;
        uri = r.uri;
        contentType = r.contentType;
        contentLength = r.contentLength;
        b = new SqlBinary(r.b);
    }
    #endregion HttpGet
    #endregion HTTP read
    #region GoogleAnalytics
    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "GoogleAnalytics_Fill")]
    public static IEnumerator GoogleAnalytics(SqlString sUsername, SqlString sPassword, SqlString sProfile, SqlString sDimensions, SqlString sMetrics, SqlDateTime dtStartDate, SqlDateTime dtEndDate, SqlInt64 iStartIndex, SqlInt64 iMaxResults)
    {
        if (sUsername.IsNull) // Special commands
            yield return new GoogleAnalytics_Row("describe", new SqlString[] { sDimensions, sMetrics });
        else
        #region Google analytics
        {
            GoogleAnalytics_Row row = new GoogleAnalytics_Row();
            row.Init();
            #region Connect to google analytics
            #endregion Connect to google analytics
            #region Logon with sUsername and sPassword
            #endregion Logon with sUsername and sPassword
            #region Open sProfile
            #endregion Open sProfile
            #region Set up result set for sDimensions, sMetrics
            #endregion Set up result set for sDimensions, sMetrics
            #region Construct query for sDimensions, sMetrics, dtStartDate, dtEndDate, iStartIndex, iMaxResults
            #endregion Construct query for sDimensions, sMetrics, dtStartDate, dtEndDate, iStartIndex, iMaxResults
            #region Execute query
            #region Attempt to fill in fields
            #endregion Attempt to fill in fields
            #region yield return
            yield return row;
            #endregion yield return
            #endregion Execute query
        }
        #endregion Google analytics
    }
    public struct GoogleAnalytics_Row
    {
        public SqlString sOutComment;
        public SqlDateTime[] dOutA;
        public SqlInt64[] iOutA;
        public SqlString[] sOutA;
        public string[] slDimensionNames;
        public string[] slMetricNames;
        public static int fC = 10;
        public GoogleAnalytics_Row(string _command, SqlString[] sData)
            : this()
        {
            #region Special commands
            Init();
            if (_command == "describe")
            #region describe
            {
                slDimensionNames = sData[0].ToString().Split("|".ToCharArray());
                slMetricNames = sData[1].ToString().Split("|".ToCharArray());
                StringBuilder sbComment = new StringBuilder("CREATE VIEW MyViewName AS\nSELECT  ");
                for (int i = 0; i < fC; i++)
                {
                    dOutA[i] = SqlDateTime.Null;
                    iOutA[i] = SqlInt64.Null;
                    if (i < slDimensionNames.Length)
                    {
                        sOutA[i] = slDimensionNames[i];
                        sbComment.AppendFormat("\n\ts{0} AS {1},", i, sOutA[i].ToString().Replace("ga:", "sDim_"));
                        sbComment.AppendFormat("i{0} AS {1},", i, sOutA[i].ToString().Replace("ga:", "iDim_"));
                        sbComment.AppendFormat("d{0} AS {1},", i, sOutA[i].ToString().Replace("ga:", "dDim_"));
                    }
                    else if (i < slDimensionNames.Length + slMetricNames.Length)
                    {
                        sOutA[i] = slMetricNames[i - slDimensionNames.Length];
                        sbComment.AppendFormat("\n\ts{0} AS {1},", i, sOutA[i].ToString().Replace("ga:", "sMeasure_"));
                        sbComment.AppendFormat("i{0} AS {1},", i, sOutA[i].ToString().Replace("ga:", "iMeasure_"));
                        sbComment.AppendFormat("d{0} AS {1},", i, sOutA[i].ToString().Replace("ga:", "dMeasure_"));
                    }
                    else
                        sOutA[i] = SqlString.Null;
                }
                sbComment.Remove(sbComment.Length - 2, 2);
                sbComment.Append("\nFROM Gorm.GoogleAnalytics(...)");
                sOutComment = sbComment.ToString();
            }
            #endregion describe
            else
                sOutA[0] = _command;
            #endregion Special commands
        }
        public void Init()
        {
            dOutA = new SqlDateTime[fC];
            iOutA = new SqlInt64[fC];
            sOutA = new SqlString[fC];
            sOutComment = SqlString.Null;
            for (int i = 0; i < fC; i++)
            {
                dOutA[i] = SqlDateTime.Null;
                iOutA[i] = SqlInt64.Null;
                sOutA[i] = SqlString.Null;
            }
        }
    }
    public static void GoogleAnalytics_Fill(object row, out SqlString sComment
        , out SqlString s0, out SqlInt64 i0, out SqlDateTime d0
        , out SqlString s1, out SqlInt64 i1, out SqlDateTime d1
        , out SqlString s2, out SqlInt64 i2, out SqlDateTime d2
        , out SqlString s3, out SqlInt64 i3, out SqlDateTime d3
        , out SqlString s4, out SqlInt64 i4, out SqlDateTime d4
        , out SqlString s5, out SqlInt64 i5, out SqlDateTime d5
        , out SqlString s6, out SqlInt64 i6, out SqlDateTime d6
        , out SqlString s7, out SqlInt64 i7, out SqlDateTime d7
        , out SqlString s8, out SqlInt64 i8, out SqlDateTime d8
        , out SqlString s9, out SqlInt64 i9, out SqlDateTime d9)
    {
        GoogleAnalytics_Row r = (GoogleAnalytics_Row)row;
        sComment = r.sOutComment;
        s0 = r.sOutA[0]; i0 = r.iOutA[0]; d0 = r.dOutA[0];
        s1 = r.sOutA[1]; i1 = r.iOutA[1]; d1 = r.dOutA[1];
        s2 = r.sOutA[2]; i2 = r.iOutA[2]; d2 = r.dOutA[2];
        s3 = r.sOutA[3]; i3 = r.iOutA[3]; d3 = r.dOutA[3];
        s4 = r.sOutA[4]; i4 = r.iOutA[4]; d4 = r.dOutA[4];
        s5 = r.sOutA[5]; i5 = r.iOutA[5]; d5 = r.dOutA[5];
        s6 = r.sOutA[6]; i6 = r.iOutA[6]; d6 = r.dOutA[6];
        s7 = r.sOutA[7]; i7 = r.iOutA[7]; d7 = r.dOutA[7];
        s8 = r.sOutA[8]; i8 = r.iOutA[8]; d8 = r.dOutA[8];
        s9 = r.sOutA[9]; i9 = r.iOutA[9]; d9 = r.dOutA[9];
    }
    #endregion GoogleAnalytics
    #region File write
    private class ExQ_Options
    {
        public bool overwrite, splitQueryOnSemicolon, requrseForRowsInQuery, savePrintOutput, dontSaveRecordOutput, printDescription;
        private SqlInt64 originalOptions;
        public ExQ_Options(SqlInt64 options)
        {
            originalOptions = options;
            Int64 opt = options.IsNull == true ? 0 : options.Value;
            overwrite = (opt & 1) == 1; //+ "\n\t\t1\tOverwrite destination"
            splitQueryOnSemicolon = (opt & 2) == 2; //+ "\n\t\t2\tSplit on ';'"
            requrseForRowsInQuery = (opt & 4) == 4; //+ "\n\t\t4\tRecurse for rows in query (first three fields)"
            savePrintOutput = (opt & 8) == 8;//+ "\n\t\t8\tSave \"PRINT\" output"
            dontSaveRecordOutput = (opt & 16) == 16;//+ "\n\t\t16\tDon't save recordset output"
            printDescription = (opt & 32) == 32;//+ "\n\t\t32\tPRINT descriptons of what is done"
        }
        public static string Usage
        {
            get
            {
                return "Usage: ExQ @queryOrQueries NVARCHAR(MAX), @fileOrFiles NVARCHAR(MAX), @options INT"
                    + "\n\t@queryOrQueries\tQuery (NULL for this message)"
                    + "\n\t@fileOrFiles\tDestination"
                    + "\n\t@options\t"
                    + "\n\t\t1\tOverwrite destination"
                    + "\n\t\t2\tSplit on ';'"
                    + "\n\t\t4\tRecurse for rows in query (first three fields)"
                    + "\n\t\t8\tSave \"PRINT\" output"
                    + "\n\t\t16\tDon't save recordset output"
                    + "\n\t\t32\tPRINT descriptons of what is done"
                    + "";
            }
        }
        public override string ToString()
        {
            return "Overwrite:" + overwrite.ToString()
                + ", splitQueryOnSemicolon:" + splitQueryOnSemicolon.ToString()
                + ", requrseForRowsInQuery:" + requrseForRowsInQuery.ToString()
                + ", savePrintOutput:" + savePrintOutput.ToString()
                + ", dontSaveRecordOutput:" + dontSaveRecordOutput.ToString()
                + ", printDescription:" + printDescription.ToString()
                + "";
        }
        public SqlInt64 WithoutSplitQuery()
        {
            if (originalOptions.IsNull)
                return originalOptions; // Null is Null :)
            return new SqlInt64(originalOptions.Value & (~2)); // strip the "split semicolon options" flag
        }
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ExQ(SqlString queryOrQueries, SqlString fileOrFiles, SqlInt64 options)
    {
        ExQ_Options exqOptions = new ExQ_Options(options);
        #region Print description
        if (exqOptions.printDescription == true)
            using (SqlContextConnection connection = new SqlContextConnection())
                Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Execute and save results"
                    + "\n\tQuery: " + queryOrQueries.Value
                    + "\n\t" + (fileOrFiles.IsNull ? "No file(s)" : "File(s): " + fileOrFiles.Value)
                    + "\n\tOptions: " + exqOptions.ToString()
                    );
        #endregion Print description
        #region Special handling (usage, recurse, multi-query)
        #region Usage
        if (queryOrQueries.IsNull == true)
            using (SqlContextConnection connection = new SqlContextConnection())
                Microsoft.SqlServer.Server.SqlContext.Pipe.Send(ExQ_Options.Usage);
        #endregion Usage
        else if (exqOptions.splitQueryOnSemicolon)
        #region Split on ";"
        {
            foreach (string sQuery in queryOrQueries.Value.Split(';'))
                ExQ(sQuery, fileOrFiles, exqOptions.WithoutSplitQuery());
        }
        #endregion Split on ";"
        else if (exqOptions.requrseForRowsInQuery)
        #region Recurse for rows in query
        {
            using (SqlContextConnection connection = new SqlContextConnection())
                Microsoft.SqlServer.Server.SqlContext.Pipe.Send("TODO: Recurse for rows in query");
        }
        #endregion Recurse for rows in query
        else
        #endregion Special handling (usage, recurse, multi-query)
        #region Execute and save results
        {
            using (SqlContextConnection connection = new SqlContextConnection())
            {
                //connection.Open();
                //System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("select @@version", connection);
                //System.Data.SqlClient.SqlDataReader r = command.ExecuteReader();
                //Microsoft.SqlServer.Server.SqlContext.Pipe.Send(r);
            }
        }
        #endregion Show usage
    }
    #endregion File write
}